import mysql from 'mysql2/promise';
import dotenv from 'dotenv';

dotenv.config();

const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'Z159v677',
  database: process.env.DB_NAME || 'weight_stock_db',
  port: parseInt(process.env.DB_PORT || '3306'),
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
};

// 创建连接池
export const pool = mysql.createPool(dbConfig);

// 初始化数据库
export async function initDatabase() {
  try {
    // 创建数据库（如果不存在）
    const connection = await mysql.createConnection({
      host: dbConfig.host,
      user: dbConfig.user,
      password: dbConfig.password,
      port: dbConfig.port
    });
    
    await connection.execute(`CREATE DATABASE IF NOT EXISTS ${dbConfig.database}`);
    await connection.end();

    // 创建表
    await createTables();
    
    console.log('数据库初始化成功');
  } catch (error) {
    console.error('数据库初始化失败:', error);
    throw error;
  }
}

async function createTables() {
  const connection = await pool.getConnection();
  
  try {
    // 体重记录表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS weight_records (
        id INT AUTO_INCREMENT PRIMARY KEY,
        weight DECIMAL(5,2) NOT NULL COMMENT '体重(kg)',
        record_time DATETIME NOT NULL COMMENT '记录时间',
        record_type ENUM('morning', 'evening', 'anytime') NOT NULL DEFAULT 'anytime' COMMENT '记录类型',
        note TEXT COMMENT '备注',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='体重记录表'
    `);

    // 运动记录表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS exercise_records (
        id INT AUTO_INCREMENT PRIMARY KEY,
        date DATE NOT NULL COMMENT '日期',
        duration INT NOT NULL DEFAULT 0 COMMENT '运动时长(分钟)',
        type VARCHAR(50) COMMENT '运动类型',
        calories INT COMMENT '消耗卡路里',
        note TEXT COMMENT '备注',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY unique_date (date)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运动记录表'
    `);

    // 目标设置表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS weight_goals (
        id INT AUTO_INCREMENT PRIMARY KEY,
        target_weight DECIMAL(5,2) NOT NULL COMMENT '目标体重',
        start_date DATE NOT NULL COMMENT '开始日期',
        target_date DATE COMMENT '目标日期',
        is_active BOOLEAN DEFAULT TRUE COMMENT '是否激活',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='减重目标表'
    `);

    console.log('数据表创建成功');
  } finally {
    connection.release();
  }
}

// 测试数据库连接
export async function testConnection() {
  try {
    const connection = await pool.getConnection();
    await connection.execute('SELECT 1');
    connection.release();
    return true;
  } catch (error) {
    console.error('数据库连接测试失败:', error);
    return false;
  }
}
